package com.example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Example {
    // JDBC driver of HSQL database
    private static final String CLASS_NAME = "org.hsqldb.jdbcDriver";
    
    // URL, user name, password for connection
    private static final String URL = "jdbc:hsqldb:mem:sampledb";
    private static final String USER_NAME = "sa";
    private static final String PASSWORD = "";

    // SQL strings to create table and insert sample data
    private static final String BANK_USER_CREATE = "CREATE TABLE BANK_USER_CREATE (USER_ID BIGINT PRIMARY KEY, USER_NAME VARCHAR(30) NOT NULL,USER_PASSWORD VARCHAR(30) NOT NULL)";
    private static final String BANK_USER_INSERT_01 = "INSERT INTO BANK_USER_CREATE (USER_ID, USER_NAME, USER_PASSWORD) VALUES(1,'sampleuser01','123456')";
    private static final String BANK_USER_INSERT_02 = "INSERT INTO BANK_USER_CREATE (USER_ID, USER_NAME, USER_PASSWORD) VALUES(2,'sampleuser02','123456')";
    
    public static void main(String args[]) {
        try {
            // Load the database driver, get a connection to the database
            Class.forName(CLASS_NAME);
            Connection conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
            
            // Get a statement from the connection
            Statement stmt = conn.createStatement();

            // Add SQL string to statement as batch
            stmt.addBatch(BANK_USER_CREATE);
            stmt.addBatch(BANK_USER_INSERT_01);
            stmt.addBatch(BANK_USER_INSERT_02);
            
            // then execute the batch
            conn.setAutoCommit(false);
            int[] updateCount = stmt.executeBatch();
            conn.commit();
            conn.setAutoCommit(true);
            
            // Print out the result of batch
            for (int i = 0; i < updateCount.length; i++) {
                System.out.println("Batch result: " + updateCount[i]);
            }

            // Execute a simple query to verify the data was inserted or not
            ResultSet rs = stmt.executeQuery("SELECT * FROM BANK_USER_CREATE");
            while (rs.next()) {
                System.out.println("User name: " + rs.getString("USER_NAME"));
            }

            // Close the result set, statement and the connection
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            System.out.println("SQL Exception:");
            se.printStackTrace();
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}
